

local file_path_in "/tochange/"

set more off

timer clear 1
timer on 1


*************************************************************************************** 
* analyze spillover of the shock to nearby homes
*************************************************************************************** 

set seed 12345678

*** new homes sold by builders. reshape to level of zip and month ***
use "`file_path_in'//HOUSEBUILDERshockHHI.dta", clear
keep if error==0
keep if small==0
drop if nfipsyr<=2  

keep if shockdelta0609ZIL_fips!=0  
drop if zip==""

keep if year==2009

set sortseed 12345678
sort zip date
bysort zip date: gen nbuild = _n
bysort zip date: gen total_builders = nbuild[_N]
order zip date seller nbuild saleamount shockdelta0609ZIL_fips
rename LATITUDE latitude
rename LONGITUDE longitude
keep latitude longitude shockdelta0609ZIL_fips zip date nbuild total_builders
drop if shockdelta0609ZIL_fips==.

* clean latitude longitude variables 
replace longitude = substr(longitude,1,8)  
destring latitude, replace
destring longitude, replace
replace latitude = latitude / 1000000
replace longitude = longitude / 100000
replace longitude = longitude*(-1)
drop if latitude==0 | longitude==0
drop if mi(latitude) | mi(longitude)

* reshape to become zip by month index
reshape  wide latitude longitude shockdelta0609ZIL_fips, i(zip date total_builders) j(nbuild) 

* create 1 month forward
sort zip date
gen forwarddate = date+1
format forwarddate %tm
order zip date forwarddate
rename date actual_date
rename forwarddate date

saveold "`file_path_in'/zipcode_latlong.dta", version(13) replace

*** use resales of newly built homes
use "`file_path_in'//builder county/resales_of_new_homes.dta", clear 

keep if year==2009
rename LATITUDE latitude
rename LONGITUDE longitude
rename UNIVERSAL_BUILDING_SQUARE_FEET sqfeet
destring sqfeet, replace
rename TOTAL_BATHS_CALCULATED baths
destring PROPERTY_INDICATOR_CODE, gen(prop_type)
label var sqfeet "Sq. Ft."
label var baths "Baths"

duplicates tag APN_UNFORMATTED APN_SEQUENCE_NUMBER FIPS_CODE , gen(ndups)
keep if ndups==0  // dropping homes which sell twice during 2009

* merge in county level house prices
merge m:1 FIPS_CODE date using "`file_path_in'/house price index/allHPchgs.dta", 
keep if _merge==3 	
drop _merge

* clean latitude longitude variables 
replace longitude = substr(longitude,1,8)  
destring latitude, replace
destring longitude, replace
replace latitude = latitude / 1000000
replace longitude = longitude / 100000
replace longitude = longitude*(-1)
drop if latitude==0 | longitude==0
drop if mi(latitude) | mi(longitude)

* merge in locations and shock of builders
merge m:1 zip date using "`file_path_in'/zipcode_latlong.dta", 

save "`file_path_in'/temp_spillover2.dta", replace

use "`file_path_in'/temp_spillover2.dta", clear 

*** calculate distance between each home and the other builders in that zipcode and month.
summ total_build , d
local maxbuild = `r(max)'
di `maxbuild'
local thres 100
levelsof total_build, local(all_build)
foreach c of local all_build {
	di "`c'"
	cap confirm var latitude`c'
	if !_rc {
		if `c'<72 {
			*set seed 12345678
			geodist latitude longitude latitude`c' longitude`c', gen(distance`c')
			replace distance`c' = 1 if distance`c'>`thres' | mi(distance`c')  
			replace shockdelta0609ZIL_fips`c' = 0 if mi(shockdelta0609ZIL_fips`c') | distance`c'>`thres'
			gen big`c' = round(distance`c'*10000000)  
		}
	}
	else {
		di "latitude`c' does not exist"
	}		
}

** aggregate over many builder shocks in one zipcode
gen shock_dist = 1/(distance1)*shockdelta0609ZIL_fips1 + 1/(distance2)*shockdelta0609ZIL_fips2  + 1/(distance3)*shockdelta0609ZIL_fips3  + 1/(distance4)*shockdelta0609ZIL_fips4  + 1/(distance5)*shockdelta0609ZIL_fips5  + 1/(distance6)*shockdelta0609ZIL_fips6  + 1/(distance7)*shockdelta0609ZIL_fips7  + 1/(distance8)*shockdelta0609ZIL_fips8  + 1/(distance9)*shockdelta0609ZIL_fips9  ///
				+ 1/(distance10)*shockdelta0609ZIL_fips10 + 1/(distance11)*shockdelta0609ZIL_fips11 + 1/(distance12)*shockdelta0609ZIL_fips12 + 1/(distance13)*shockdelta0609ZIL_fips13 + 1/(distance14)*shockdelta0609ZIL_fips14 + 1/(distance15)*shockdelta0609ZIL_fips15 + 1/(distance16)*shockdelta0609ZIL_fips16 + 1/(distance17)*shockdelta0609ZIL_fips17 + 1/(distance18)*shockdelta0609ZIL_fips18  ///
				+ 1/(distance19)*shockdelta0609ZIL_fips19 + 1/(distance20)*shockdelta0609ZIL_fips20 + 1/(distance21)*shockdelta0609ZIL_fips21 + 1/(distance22)*shockdelta0609ZIL_fips22 + 1/(distance23)*shockdelta0609ZIL_fips23 + 1/(distance24)*shockdelta0609ZIL_fips24 + 1/(distance25)*shockdelta0609ZIL_fips25 + 1/(distance26)*shockdelta0609ZIL_fips26 + 1/(distance27)*shockdelta0609ZIL_fips27  ///
				+ 1/(distance28)*shockdelta0609ZIL_fips28 + 1/(distance29)*shockdelta0609ZIL_fips29 + 1/(distance30)*shockdelta0609ZIL_fips30 + 1/(distance31)*shockdelta0609ZIL_fips31 + 1/(distance32)*shockdelta0609ZIL_fips32 + 1/(distance33)*shockdelta0609ZIL_fips33 + 1/(distance34)*shockdelta0609ZIL_fips34 + 1/(distance35)*shockdelta0609ZIL_fips35 + 1/(distance36)*shockdelta0609ZIL_fips36  ///
				 + 1/(distance37)*shockdelta0609ZIL_fips37  + 1/(distance38)*shockdelta0609ZIL_fips38  + 1/(distance39)*shockdelta0609ZIL_fips39  + 1/(distance40)*shockdelta0609ZIL_fips40  + 1/(41)*shockdelta0609ZIL_fips41  + 1/(distance42)*shockdelta0609ZIL_fips42  + 1/(distance43)*shockdelta0609ZIL_fips43  + 1/(distance44)*shockdelta0609ZIL_fips44  + 1/(distance45)*shockdelta0609ZIL_fips45  ///
				  + 1/(distance47)*shockdelta0609ZIL_fips47  + 1/(distance48)*shockdelta0609ZIL_fips48  + 1/(distance49)*shockdelta0609ZIL_fips49  + 1/(distance51)*shockdelta0609ZIL_fips51  + 1/(distance52)*shockdelta0609ZIL_fips52  + 1/(distance53)*shockdelta0609ZIL_fips53  + 1/(distance55)*shockdelta0609ZIL_fips55  + 1/(distance59)*shockdelta0609ZIL_fips59  + 1/(distance62)*shockdelta0609ZIL_fips62  
				
order zip date seller latitude latitude1 latitude2 latitude3 shockdelta0609ZIL_fips1 shockdelta0609ZIL_fips2  distance*
gen lnhp = log(saleamount)
gen dist_fp1 = 1/distance1
gen dist_fp2 = 1/distance2

label var shock_dist "Aggr. Shock"

* shock of nearest home 
destring zip, gen(zip2)
reghdfe lnhp shock_dist loghpZIL if !mi(sqfeet) & !mi(baths) & !mi(prop_type), absorb(zip2 date i.zip2#i.date) vce(cluster FIPS_CODE) 
estadd local fixedfips "Yes" , replace
estadd local fixedyrfips "Yes" , replace
estadd local hpZIL "Yes", replace
estadd local sqfeet "No", replace
estadd local baths "No", replace
estadd local fixedprop "No", replace
eststo c1

reghdfe lnhp shock_dist sqfeet loghpZIL if !mi(sqfeet) & !mi(baths) & !mi(prop_type), absorb(zip2 date i.zip2#i.date) vce(cluster FIPS_CODE) 
estadd local fixedfips "Yes" , replace
estadd local fixedyrfips "Yes" , replace
estadd local hpZIL "Yes", replace
estadd local sqfeet "Yes", replace
estadd local baths "No", replace
estadd local fixedprop "No", replace
eststo c2

reghdfe lnhp shock_dist sqfeet baths loghpZIL if !mi(sqfeet) & !mi(baths) & !mi(prop_type), absorb(zip2 date i.zip2#i.date) vce(cluster FIPS_CODE) 
estadd local fixedfips "Yes" , replace
estadd local fixedyrfips "Yes" , replace
estadd local hpZIL "Yes", replace
estadd local sqfeet "Yes", replace
estadd local baths "Yes", replace
estadd local fixedprop "No", replace
eststo c3

reghdfe lnhp shock_dist sqfeet baths i.prop_type loghpZIL if !mi(sqfeet) & !mi(baths) & !mi(prop_type), absorb(zip2 ) vce(cluster FIPS_CODE) 
estadd local fixedfips "Yes" , replace
estadd local fixedyrfips "No" , replace
estadd local hpZIL "Yes", replace
estadd local sqfeet "Yes", replace
estadd local baths "Yes", replace
estadd local fixedprop "Yes", replace
eststo c4

reghdfe lnhp shock_dist sqfeet baths i.prop_type loghpZIL if !mi(sqfeet) & !mi(baths) & !mi(prop_type), absorb(zip2 date i.zip2#i.date) vce(cluster FIPS_CODE) 
estadd local fixedfips "Yes" , replace
estadd local fixedyrfips "Yes" , replace
estadd local hpZIL "Yes", replace
estadd local sqfeet "Yes", replace
estadd local baths "Yes", replace
estadd local fixedprop "Yes", replace
eststo c5
		
esttab  c1 c2 c3 c4 c5 using "`file_path_in'/reg output/spillover_resales.csv",   replace se label nonotes s(fixedfips fixedyrfips hpZIL sqfeet baths fixedprop r2 N , ///
	label("Zip FE" "Yr x Mo. x Zip FE" "County HP" "Sq. Ft." "Baths" "Prop. Type" "\$ R^2 \$")) substitute(\_ _) nomtitles drop(sqfeet baths *prop_type* loghpZIL)  mgroups("All Firms", pattern(1 0 0 0 0))    ///
		starlevels(* 0.10 ** 0.05 *** 0.01)
esttab  c1 c2 c3 c4 c5 using "`file_path_in'/reg output/spillover_resales.tex",   replace se label nonotes s(fixedfips fixedyrfips hpZIL sqfeet baths fixedprop r2 N , ///
	label("Zip FE" "Yr x Mo. x Zip FE" "County HP" "Sq. Ft." "Baths" "Prop. Type" "\$ R^2 \$")) drop() substitute(\_ _) nomtitles drop(sqfeet baths *prop_type* loghpZIL)   mgroups("All Firms", pattern(1 0 0 0 0)) ///
		starlevels(* 0.10 ** 0.05 *** 0.01)

timer off 1
timer list 1
